package com.eden.market.geoedu;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class BDAccess {

	/*TABLE NAME*/
	private static final String CAPITAL_CITY_TABLE = "capital_city";
	private static final String HIGHSCORE_TABLE = "highscore_table";
	private static final String DATABASE_NAME = "geoedu_android";
	private static final String VERSION_TABLE = "version";


	/*COLUMN CAPITAL NAME*/
	private static final String KEY_ID_CAPITAL = "id_capital";
	private static final String KEY_CITY_NAME = "city_name";
	private static final String KEY_COUNTRY_NAME = "country_name";
	private static final String KEY_LATITUDE = "latitude";
	private static final String KEY_LONGITUDE = "longitude";
	private static final String KEY_DISTANCE_HIT = "distance_hit";
	private static final String KEY_MAP_ID = "id_map";

	/*COLUMN HIGHSCORE NAME*/
	private static final String KEY_ID_HIGHSCORE = "id_highscore";
	private static final String KEY_USER_NAME = "user_name";
	private static final String KEY_SCORE = "score";
	private static final String KEY_ID_GAME = "id_game";
	
	/*COLUMN VERSION NAME*/
	private static final String KEY_ID_VERSION = "id_version";
	private static final String KEY_VERSION_NUMBER = "number_version";

	private final Context mCtx;

	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;
	//private SQLiteQueryBuilder mbbuilder;

	private static final String TAG = "BDAccess";

	private static final String CREATE_CAPITAL_CITY_TABLE = "CREATE TABLE IF NOT EXISTS " + CAPITAL_CITY_TABLE + " ("
	+ KEY_ID_CAPITAL + " INTEGER PRIMARY KEY AUTOINCREMENT,"
	+ KEY_CITY_NAME + " VARCHAR(250) NOT NULL,"
	+ KEY_COUNTRY_NAME + " VARCHAR(250) NOT NULL,"
	+ KEY_LATITUDE + " INTEGER NOT NULL,"
	+ KEY_LONGITUDE + " INTEGER NOT NULL,"
	+ KEY_DISTANCE_HIT + " INTEGER NULL,"
	+ KEY_MAP_ID + " INTEGER NULL"
	+ ")";
	
	private static final String CREATE_VERSION_TABLE = "CREATE TABLE IF NOT EXISTS " + VERSION_TABLE + " ("
	+ KEY_ID_VERSION + " INTEGER PRIMARY KEY AUTOINCREMENT,"
	+ KEY_VERSION_NUMBER + " INTEGER NULL"
	+ ")";

	private static final String CREATE_HIGHSCORE_TABLE ="CREATE TABLE IF NOT EXISTS "  + HIGHSCORE_TABLE + " ("
	+ KEY_ID_HIGHSCORE + " INTEGER PRIMARY KEY AUTOINCREMENT,"
	+ KEY_USER_NAME + " VARCHAR(25) NOT NULL,"
	+ KEY_SCORE + " INTEGER NOT NULL,"
	+ KEY_ID_GAME + " INTEGER NOT NULL"
	+ ")";


	public BDAccess(Context ctx) {
		this.mCtx = ctx;
	}


	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, 1);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(CREATE_CAPITAL_CITY_TABLE);
			db.execSQL(CREATE_HIGHSCORE_TABLE);
			db.execSQL(CREATE_VERSION_TABLE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS " + CAPITAL_CITY_TABLE);
			// Drop table if big new version with new city list
			//db.execSQL("DROP TABLE IF EXISTS " + HIGHSCORE_TABLE);
			onCreate(db);
		}
	}

	//---opens the database---
	public void open(int version) throws SQLException 
	{
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
	}
	
	//---opens the database---
	public void open() throws SQLException 
	{
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
	}

	//---closes the database---    
	public void close() 
	{
		mDbHelper.close();
	}

	public long insertCapitalCity(CityBean city) 
	{
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_CITY_NAME, city.getCityName());
		initialValues.put(KEY_COUNTRY_NAME, city.getCityCountry());
		initialValues.put(KEY_LATITUDE, city.getCityLatitude());
		initialValues.put(KEY_LONGITUDE, city.getCityLongitude());
		initialValues.put(KEY_MAP_ID, city.getMapId());
		return mDb.insert(CAPITAL_CITY_TABLE, null, initialValues);
	}
	
	public long updateVersion(int version) 
	{
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_VERSION_NUMBER, version);
		return mDb.insert(VERSION_TABLE, null, initialValues);
	}
	
	public int getVersion() {
		
		mDbHelper.onCreate(mDb);
		String[] aTableCollonne=new String[] {KEY_VERSION_NUMBER};
		String orderBy= KEY_VERSION_NUMBER + " DESC" ;
		String limit= "1";
		Cursor objCursor = mDb.query(VERSION_TABLE ,aTableCollonne,null,null,null,null,orderBy,limit);
		int iVersion = objCursor.getColumnIndex(KEY_VERSION_NUMBER);
		objCursor.moveToFirst();

		int version = 1;
		if (objCursor != null) {            	 
			if (objCursor.isFirst()) { 
				do {
					version = objCursor.getInt(iVersion) ;
				} while(objCursor.moveToNext());
			}
		}

		objCursor.close();
		return version;		
		
		
	}

	public long insertHighScore(HighScoreBean score) 
	{
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_USER_NAME, score.getUserName());
		initialValues.put(KEY_SCORE, score.getScore());
		initialValues.put(KEY_ID_GAME, score.getGameId());
		return mDb.insert(HIGHSCORE_TABLE, null, initialValues);
	}

	public ArrayList<CityBean> get_list_city(int mapId){

		String[] aTableCollonne=new String[] {KEY_CITY_NAME, KEY_COUNTRY_NAME, KEY_LATITUDE, KEY_LONGITUDE};
		String sFiltre= KEY_MAP_ID + "='" + mapId + "'";
		String orderBy= "RANDOM()";
		String limit= "20";
		Cursor objCursor = mDb.query(CAPITAL_CITY_TABLE,aTableCollonne,sFiltre,null,null,null,orderBy,limit);
		int iCityName = objCursor.getColumnIndex(KEY_CITY_NAME);
		int iCountryName = objCursor.getColumnIndex(KEY_COUNTRY_NAME);
		int iLatitude = objCursor.getColumnIndex(KEY_LATITUDE);
		int iLongitude = objCursor.getColumnIndex(KEY_LONGITUDE);
		objCursor.moveToFirst();
		ArrayList<CityBean> cityList = new ArrayList<CityBean>();

		if (objCursor != null) {            	 
			if (objCursor.isFirst()) { 
				do {
					CityBean city = new CityBean();
					city.setCityName(objCursor.getString(iCityName)) ;
					city.setCityCountry(objCursor.getString(iCountryName)) ;
					city.setCityLatitude(objCursor.getInt(iLatitude)) ;
					city.setCityLongitude(objCursor.getInt(iLongitude)) ;
					cityList.add(city);
				} while(objCursor.moveToNext());
			}
		}

		objCursor.close();
		return cityList;
	}

	public ArrayList<HighScoreBean> get_list_score(int mapId) {

		String[] aTableCollonne=new String[] {KEY_USER_NAME, KEY_SCORE, KEY_ID_GAME};
		String sFiltre= KEY_ID_GAME + "='" + mapId + "'";
		Cursor objCursor = mDb.query(HIGHSCORE_TABLE,aTableCollonne,sFiltre,null,null,null,null,null);
		int iUserName = objCursor.getColumnIndex(KEY_USER_NAME);
		int iScore = objCursor.getColumnIndex(KEY_SCORE);
		int iGameID = objCursor.getColumnIndex(KEY_ID_GAME);
		objCursor.moveToFirst();
		ArrayList<HighScoreBean> scoreList = new ArrayList<HighScoreBean>();

		if (objCursor != null) {            	 
			if (objCursor.isFirst()) { 
				do {
					HighScoreBean score = new HighScoreBean();
					score.setUserName(objCursor.getString(iUserName)) ;
					score.setScore(objCursor.getInt(iScore)) ;
					score.setGameId(objCursor.getInt(iGameID)) ;
					scoreList.add(score);
				} while(objCursor.moveToNext());
			}
		}

		objCursor.close();
		return scoreList;
	}


	public ArrayList<HighScoreBean> get_list_scores(int mapId, int top) {

		String[] aTableCollonne=new String[] {KEY_USER_NAME, KEY_SCORE, KEY_ID_GAME};
		String sFiltre= KEY_ID_GAME + "='" + mapId + "'";
		String orderBy = KEY_SCORE + " DESC";
		String limit = Integer.toString(top);
		Cursor objCursor = mDb.query(HIGHSCORE_TABLE,aTableCollonne,sFiltre,null,null,null,orderBy,limit);
		int iUserName = objCursor.getColumnIndex(KEY_USER_NAME);
		int iScore = objCursor.getColumnIndex(KEY_SCORE);
		int iGameID = objCursor.getColumnIndex(KEY_ID_GAME);
		objCursor.moveToFirst();
		ArrayList<HighScoreBean> scoreList = new ArrayList<HighScoreBean>();

		if (objCursor != null) {            	 
			if (objCursor.isFirst()) { 
				do {
					HighScoreBean score = new HighScoreBean();
					score.setUserName(objCursor.getString(iUserName)) ;
					score.setScore(objCursor.getInt(iScore)) ;
					score.setGameId(objCursor.getInt(iGameID)) ;
					scoreList.add(score);
				} while(objCursor.moveToNext());
			}
		}

		objCursor.close();
		return scoreList;
	}

	public void update_bdd(){
		int newversion = mDb.getVersion()+1;
		mDbHelper.onUpgrade(mDb, mDb.getVersion(), newversion);	
		//mDbHelper.onCreate(mDb);
	}



}
